Excel BI - Excel Challenge 692

excel-challenges
excel-formulas
🔰 LList the team which won all their matches
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 692

Challenge Description

🔰 LList the team which won all their matches

Solutions

library(tidyverse)
library(readxl)

path = "Excel/692 Team having won all matches.xlsx"
input = read_excel(path, range = "A1:C11")
test  = read_excel(path, range = "E1:E2")

result = input %>%
  mutate(rn = row_number()) %>%
  unite("Teams", c(`Team 1`, `Team 2`), sep = "-") %>%
  separate_rows(c(Result, Teams), sep = "-") %>%
  mutate(verdict = ifelse(Result == max(Result), "WIN", "LOSE"), .by = rn) %>%
  summarise(n = n(), .by = c(verdict, Teams)) %>%
  filter(n == 4, verdict == "WIN") %>%
  select(Teams)

all.equal(result$Teams, test$`Answer Expected`)
#> [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd

path = "692 Team having won all matches.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=11)
test = pd.read_excel(path, usecols="E", nrows=1)

input['rn'] = range(1, len(input) + 1)
input['Teams'] = input['Team 1'] + "-" + input['Team 2']
input = input.drop(columns=['Team 1', 'Team 2'])
input = input.assign(Result=input['Result'].astype(str).str.split('-'),
                           Teams=input['Teams'].str.split('-')).explode(['Result', 'Teams'])
input['verdict'] = input.groupby('rn')['Result'].transform(
    lambda x: ['WIN' if val == max(x.astype(int)) else 'LOSE' for val in x.astype(int)]
)
summary = input.groupby(['verdict', 'Teams']).size().reset_index(name='n')
result = summary[(summary['n'] == 4) & (summary['verdict'] == 'WIN')][['Teams']].reset_index(drop=True)

print(test["Answer Expected"].equals(result["Teams"]))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.